﻿using BCCommon;
using BCEntity.WeChatComponent.WeChatKeyword;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace BCData.WeChatComponent.WeChatKeyword
{
    /// <summary>
    /// 微信关键字数据处理
    /// </summary>
    public class WeChatKeywordData: IWeChatKeywordData
    {
        private const string QUERY_SQL = @"SELECT A.WeChatKeywordId,A.Keyword,A.Content,A.Remark,A.MediaId,A.ThumbMediaId,A.ReplyType,A.CreateTime,B.LocalUrl
                                            FROM WeChatKeyword A
                                                LEFT JOIN WeChatMediaRecord B
                                                    ON A.MediaId=B.MediaId";

        protected readonly IDatabaseContext databaseContext;

        public WeChatKeywordData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        /// <summary>
        /// 创建微信关键字信息
        /// </summary>
        /// <param name="entity">微信关键字信息</param>
        /// <returns></returns>
        public WeChatKeywordEntity Create(WeChatKeywordEntity entity)
        {
            string sql = String.Format(@"INSERT INTO WeChatKeyword(Keyword,Content,Remark,MediaId,ThumbMediaId,ReplyType)
                                                    VALUES(?Keyword,?Content,?Remark,?MediaId,?ThumbMediaId,?ReplyType);
                                        {0} WHERE A.WeChatKeywordId=LAST_INSERT_ID();", QUERY_SQL);

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Keyword", entity.Keyword, DbType.AnsiString, size: 50);
            parameters.Add("?Content", entity.Content, DbType.AnsiString, size: 500);
            parameters.Add("?Remark", entity.Remark, DbType.AnsiString, size: 200);
            parameters.Add("?MediaId", entity.MediaId, DbType.AnsiString, size: 100);
            parameters.Add("?ThumbMediaId", entity.MediaId, DbType.AnsiString, size: 100);
            parameters.Add("?ReplyType", entity.ReplyType.ToString(), DbType.AnsiString);

            return this.databaseContext.Master.QueryEntity<WeChatKeywordEntity>(sql, parameters, databaseContext.Transaction);
        }
        /// <summary>
        /// 删除一条微信关键字信息
        /// </summary>
        /// <param name="weChatKeywordId">主键</param>
        /// <returns></returns>
        public bool Delete(int weChatKeywordId)
        {
            string sql = @"DELETE FROM WeChatKeyword WHERE WeChatKeywordId=?WeChatKeywordId;";

            DynamicParameters parameter = new DynamicParameters();

            parameter.Add("?WeChatKeywordId", weChatKeywordId, DbType.Int32);

            var result = this.databaseContext.Master.Execute(sql, parameter, databaseContext.Transaction);
            return result > 0;
        }
        /// <summary>
        /// 分页获取微信关键字信息
        /// </summary>
        /// <param name="keyword">关键字</param>
        /// <param name="start">开始时间</param>
        /// <param name="end">结束时间</param>
        /// <param name="dataCount">返回数量</param>
        /// <param name="ascending">排序</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页数</param>
        /// <returns></returns>
        public IEnumerable<WeChatKeywordEntity> GetWeChatKeywordList(string keyword,DateTime? start,DateTime? end, out int dataCount,bool? ascending, int pageIndex=1, int pageSize=10)
        {
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(keyword))
            {
                wheres.Add("A.Keyword=?Keyword");
                parameters.Add("?Keyword", keyword, DbType.AnsiString, size: 50);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            string newsql = string.Format("{0} {1} ORDER BY A.CreateTime {4} LIMIT {2}, {3}; SELECT COUNT(1) FROM WeChatKeyword A {1}",
                QUERY_SQL,
                wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty,
                (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
                pageSize,
                ascending.HasValue && ascending.Value ? "ASC" : "DESC");

            var results = this.databaseContext.Slave.QueryMultiple<WeChatKeywordEntity, int>(newsql, parameters);

            dataCount = results.Item2;

            return results.Item1;
        }
        /// <summary>
        /// 获取一条微信关键字信息
        /// </summary>
        /// <param name="weChatKeywordId">主键</param>
        /// <returns></returns>
        public WeChatKeywordEntity GetWeChatKeywordById(int weChatKeywordId)
        {
            string sql = string.Format("{0} WHERE A.WeChatKeywordId=?WeChatKeywordId", QUERY_SQL);

            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?WeChatKeywordId", weChatKeywordId, DbType.Int32);

            return this.databaseContext.Slave.QueryEntity<WeChatKeywordEntity>(sql, parameters);
        }
        /// <summary>
        /// 获取一条微信关键字信息
        /// </summary>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public WeChatKeywordEntity GetWeChatKeyword(string keyword)
        {
            string sql = string.Format("{0} WHERE A.Keyword=?Keyword", QUERY_SQL);

            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?Keyword", keyword, DbType.AnsiString, size: 50);

            return this.databaseContext.Slave.QueryEntity<WeChatKeywordEntity>(sql, parameters);
        }
        /// <summary>
        /// 更新一条微信关键字信息
        /// </summary>
        /// <param name="entity">微信关键字信息</param>
        /// <returns></returns>
        public WeChatKeywordEntity Update(WeChatKeywordEntity entity)
        {
            string sql = @"UPDATE WeChatKeyword SET 
                                Keyword=?Keyword,
                                Content=?Content,
                                Remark=?Remark,
                                ThumbMediaId=?ThumbMediaId,
                                MediaId=?MediaId,
                                ReplyType=?ReplyType
                            WHERE WeChatKeywordId=?WeChatKeywordId;
                        {0} WHERE A.WeChatKeywordId=?WeChatKeywordId;";
            sql = string.Format(sql, QUERY_SQL);

            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?Keyword", entity.Keyword, DbType.AnsiString, size: 50);
            parameters.Add("?Content", entity.Content, DbType.AnsiString, size: 500);
            parameters.Add("?Remark", entity.Remark, DbType.AnsiString, size: 200);
            parameters.Add("?MediaId", entity.MediaId, DbType.AnsiString, size: 100);
            parameters.Add("?ThumbMediaId", entity.MediaId, DbType.AnsiString, size: 100);
            parameters.Add("?ReplyType", entity.ReplyType.ToString(), DbType.AnsiString);
            parameters.Add("?WeChatKeywordId", entity.WeChatKeywordId, DbType.Int32);

            return this.databaseContext.Master.QueryEntity<WeChatKeywordEntity>(sql, parameters, databaseContext.Transaction);
        }
        /// <summary>
        /// 获取所有微信关键字信息
        /// </summary>
        /// <returns></returns>
        public IEnumerable<WeChatKeywordEntity> GetWeChatKeywordAll()
        {
            return this.databaseContext.Slave.Query<WeChatKeywordEntity>(QUERY_SQL);
        }

        public bool CheckKeywordExists(string keyword)
        {
            string sql = @"SELECT 1 FROM WeChatKeyword WHERE Keyword=?Keyword;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Keyword", keyword, DbType.AnsiString, size: 50);
            object result = this.databaseContext.Slave.ExecuteScalar(sql, parameters);
            return result != null;
        }
        public bool CheckKeywordExists(int weChatKeywordId)
        {
            string sql = @"SELECT 1 FROM WeChatKeyword WHERE WeChatKeywordId=?WeChatKeywordId;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WeChatKeywordId", weChatKeywordId, DbType.Int32);
            object result = this.databaseContext.Slave.ExecuteScalar(sql, parameters);
            return result != null;
        }
    }
}
